﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;

namespace InvoiceImport
{
    public class ExcelHelper
    {
        /// <summary>
        /// 导出Excel表格
        /// </summary>
        /// <param name="list">数据集合</param>
        /// <param name="header">数据表头</param>
        /// <returns></returns>
        public static void ExportExcel(DataTable dt, string SaveFikeName)
        {
            dt.Columns.Add("a");
            dt.Rows.Add("b"); //添加一条测试数据 b
            //使用内存流来存这些byte[]
            using (MemoryStream memory = new MemoryStream())
            {
                //系列化datatable,MS已经对datatable实现了系列化接口,如果你自定义的类要系列化,实现IFormatter 就可以类似做法
                BinaryFormatter b = new BinaryFormatter();
                b.Serialize(memory, dt);
                //这里就可你想要的byte[],可以使用它来传输
                File.WriteAllBytes(SaveFikeName, memory.GetBuffer());
                memory.Close();
            }
        }
        /// <summary>
        /// DataTable转化为Byte[]
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static byte[] DataTableToByte(DataTable dt)
        {
            byte[] bytes;
            using (MemoryStream memoryStream = new MemoryStream())
            {
                BinaryFormatter binaryFormatter = new BinaryFormatter();
                binaryFormatter.Serialize(memoryStream, dt);
                bytes = memoryStream.GetBuffer();
            }
            return bytes;
        }
        public static bool ExportExcelWithAspose(DataTable data, string filepath)
        {
            try
            {
                Workbook book = new Workbook(); //创建工作簿
                Worksheet sheet = book.Worksheets[0]; //创建工作表
                Cells cells = sheet.Cells; //单元格
                //创建样式
                Style style = book.Styles[book.Styles.Add()];
                style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线  
                style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线  
                style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线  
                style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线   
                style.HorizontalAlignment = TextAlignmentType.Center; //单元格内容的水平对齐方式文字居中
                style.Font.Name = "宋体"; //字体
                //style.Font.IsBold = true; //设置粗体
                style.Font.Size = 11; //设置字体大小
                                      //style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //背景色
                                      //style.Pattern = BackgroundType.Solid;  

                int Colnum = data.Columns.Count;//表格列数 
                int Rownum = data.Rows.Count;//表格行数 
                //生成行 列名行 
                for (int i = 0; i < Colnum; i++)
                {
                    cells[0, i].PutValue(data.Columns[i].ColumnName); //添加表头
                    cells[0, i].SetStyle(style); //添加样式
                }
                //生成数据行 
                for (int i = 0; i < Rownum; i++)
                {
                    for (int k = 0; k < Colnum; k++)
                    {
                        cells[1 + i, k].PutValue(data.Rows[i][k].ToString()); //添加数据
                        cells[1 + i, k].SetStyle(style); //添加样式
                    }
                }
                sheet.AutoFitColumns(); //自适应宽
                book.Save(filepath); //保存
                GC.Collect();
            }
            catch
            {
                return false;
            }
            return true;
        }
    }
}
